package com.lecture.java8.excel.dyr.n.a004;

import com.alibaba.excel.EasyExcel;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelMerger {
    public static void main(String[] args) {
        String path = "D:\\study\\tmp\\1119\\";

        // 读取Excel1
        List<Excel1> excel1List = EasyExcel.read(path + "非平衡面板数据(过滤后).xlsx").head(Excel1.class).sheet().doReadSync();

        // 读取Excel2
        List<Excel2> excel2List = EasyExcel.read(path + "2最终结果-分析师关注.xlsx").head(Excel2.class).sheet().doReadSync();
        Map<String, Excel2> excel2Map = new HashMap<>();
        for (Excel2 excel2 : excel2List) {
            excel2Map.put(excel2.getStkcd() + "-" + excel2.getYear(), excel2);
        }

        // 读取Excel3
        List<Excel3> excel3List = EasyExcel.read(path + "3上市公司绿色创新（匹配版）00-23年.xlsx").head(Excel3.class).sheet().doReadSync();
        Map<String, Excel3> excel3Map = new HashMap<>();
        for (Excel3 excel3 : excel3List) {
            excel3Map.put(excel3.getStockCode() + "-" + excel3.getYear(), excel3);
        }

        // 读取Excel4
        List<Excel4> excel4List = EasyExcel.read(path + "4常用控制变量-已剔除未缩尾.xlsx").head(Excel4.class).sheet().doReadSync();
        Map<String, Excel4> excel4Map = new HashMap<>();
        for (Excel4 excel4 : excel4List) {
            excel4Map.put(excel4.getStkcd() + "-" + excel4.getYear(), excel4);
        }

        // 合并数据
        for (Excel1 excel1 : excel1List) {
            String key = excel1.getCode() + "-" + excel1.getYear();

            // 关联Excel2
            Excel2 excel2 = excel2Map.get(key);
            if (excel2 != null) {
                excel1.setAnalyst2(excel2.getAnalyst2());
            }

            // 关联Excel3
            Excel3 excel3 = excel3Map.get(key);
            if (excel3 != null) {
                excel1.setGreenInnovation1(excel3.getGreenInnovation1());
                excel1.setGreenInnovation2(excel3.getGreenInnovation2());
                excel1.setGreenInnovation3(excel3.getGreenInnovation3());
            }

            // 关联Excel4
            Excel4 excel4 = excel4Map.get(key);
            if (excel4 != null) {
                excel1.setShortName(excel4.getShortName());
                excel1.setIndustryName(excel4.getIndustryName());
                excel1.setIndustry1(excel4.getIndustry1());
                excel1.setIndustry2(excel4.getIndustry2());
                excel1.setFinance(excel4.getFinance());
                excel1.setStpt(excel4.getStpt());
                excel1.setManufacturing(excel4.getManufacturing());
                excel1.setBh(excel4.getBh());
                excel1.setSoe(excel4.getSoe());
                excel1.setSize(excel4.getSize());
                excel1.setLev(excel4.getLev());
                excel1.setEm(excel4.getEm());
                excel1.setRoa(excel4.getRoa());
                excel1.setRoe(excel4.getRoe());
                excel1.setGrossProfit(excel4.getGrossProfit());
                excel1.setNetProfitGrowth(excel4.getNetProfitGrowth());
                excel1.setLoss(excel4.getLoss());
                excel1.setGrowth(excel4.getGrowth());
                excel1.setAssetGrowth(excel4.getAssetGrowth());
                excel1.setRec(excel4.getRec());
                excel1.setInv(excel4.getInv());
                excel1.setIar(excel4.getIar());
                excel1.setFixed(excel4.getFixed());
                excel1.setTar(excel4.getTar());
                excel1.setCr(excel4.getCr());
                excel1.setQuick(excel4.getQuick());
                excel1.setCashFlow(excel4.getCashFlow());
                excel1.setMfee(excel4.getMfee());
                excel1.setAto(excel4.getAto());
                excel1.setBm(excel4.getBm());
                excel1.setPb(excel4.getPb());
                excel1.setTobinQ(excel4.getTobinQ());
                excel1.setSaIndex(excel4.getSaIndex());
                excel1.setSaIndexAbs(excel4.getSaIndexAbs());
                excel1.setListAge(excel4.getListAge());
                excel1.setFirmAge(excel4.getFirmAge());
                excel1.setDual(excel4.getDual());
                excel1.setBoard(excel4.getBoard());
                excel1.setIndep(excel4.getIndep());
                excel1.setMshare(excel4.getMshare());
                excel1.setFemale(excel4.getFemale());
                excel1.setEmployee(excel4.getEmployee());
                excel1.setInst(excel4.getInst());
                excel1.setTop1(excel4.getTop1());
                excel1.setTop3(excel4.getTop3());
                excel1.setHerfindahl3(excel4.getHerfindahl3());
                excel1.setHerfindahl5(excel4.getHerfindahl5());
                excel1.setBalance1(excel4.getBalance1());
                excel1.setBalance2(excel4.getBalance2());
                excel1.setOpinion(excel4.getOpinion());
                excel1.setAuditFee(excel4.getAuditFee());
                excel1.setBig4(excel4.getBig4());
                excel1.setEast(excel4.getEast());
                excel1.setWest(excel4.getWest());
                excel1.setMid(excel4.getMid());
                excel1.setListedYear(excel4.getListedYear());
                excel1.setEstablishYear(excel4.getEstablishYear());
                excel1.setDelistedYear(excel4.getDelistedYear());
                //excel1.setProvinceCode(excel4.getProvinceCode());
                //excel1.setProvince(excel4.getProvince());
                //excel1.setCityCode(excel4.getCityCode());
                //excel1.setCity(excel4.getCity());
            }
        }

        // 导出新的Excel
        EasyExcel.write(path + "非平衡面板数据(合并后).xlsx", Excel1.class).sheet("Sheet1").doWrite(excel1List);
    }
}
